import pymysql, re, json
from pymysql import OperationalError, ProgrammingError, InternalError
import traceback
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': '112358',
    'db': 'homework',
    'charset': 'utf8'
}
def connection_db():
    try:
        connection = pymysql.connect(**config)
        return connection
    except OperationalError as e:
        print('机制错误:', e)
    except InternalError as e:
        print('IP错误:', e)

def sql_insert(table_name, data):

    insert_data = [
        ('name', data[0]),
        ('types', data[1]),
        ('t', data[2])
    ]
    head = []
    values = []
    temps = []
    for item in insert_data:
        head.append(item[0])
        values.append(item[1])
        temps.append('%s')
    head = tuple(head)
    values = tuple(values)
    temps = tuple(temps)
    base_url = "insert into " + table_name
    base_url += str(head) + ' values' + str(temps)
    base_url = re.sub(r'\'', '', base_url)
    connection = connection_db()
    if connection:
        cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            cursor.execute(base_url,values)
            connection.commit()
        except:
            traceback.print_exc()
            connection.rollback()
        finally:
            cursor.close()
            connection.close()

def main():
    with open('./in_theaters.json','r') as f:
        load = json.load(f)
        resluts = load['subjects']
        for items in resluts:
            ls=[]
            ls.append(items['title'])
            str = ','.join(items['genres'])
            ls.append(str)
            ls.append(items['mainland_pubdate'])
            sql_insert('moves', ls)
    # ls = ['碟中谍6：全面瓦解', '动作,惊悚,冒险', '2018-08-31']
    # sql_insert('moves', ls)

if __name__ == '__main__':
    main()


